﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using APMABusinessLogicCoreLib.Helpers;
using System.Security.Cryptography;
using System.Text;


namespace APMABusinessLogicCoreLib
{
    public class BusinessLogicCore : IBusinessLogicCore
    {
        static BusinessLogicCore()
        {
            const string instanceName = "i3";
            const string dbName = "APMAConfiguration";
            DBHelper.ConnectionString =
                string.Format("Data Source={0}; Initial Catalog={1}; Integrated Security=SSPI;",
                              instanceName,
                              dbName);
        }

        public string GetMessageFromBL()
        {
            try
            {
                using (var dbHelper = new DBHelper())
                {
                    const string sqlStatement = "SELECT * FROM dbo.Users";

                    var result = dbHelper.ExecuteSqlStatementForDataSet(sqlStatement);

                    return result.Tables[0].Rows[0][1].ToString();
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine("WinService Exception: " + ex.Message);
                throw;
            }
        }

        public DataTable GetTasksList()
        {
            try
            {
                using (var dbHelper = new DBHelper())
                {
                    const string procedureName = "get_tasks";

                    var result = dbHelper.ExecStorProcedureDS(procedureName).Tables[0];

                    return result;
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine("WinService Exception: " + ex.Message);
                throw;
            }
        }

        public int AddNewTask(Dictionary<string, object> parameters, string login)
        {
            try
            {
                using (var dbHelper = new DBHelper())
                {
                    const string procedureName = "add_task";

                    var result = dbHelper.ExecStorProcedureInt(procedureName, parameters);
                    
                    AddToLog("New task added", login);

                    return result;
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine("WinService Exception: " + ex.Message);
                throw;
            }
        }

        public int RemoveTask(int id, string login)
        {
            try
            {
                using (var dbHelper = new DBHelper())
                {
                    const string procedureName = "remove_task";

                    var parameters = new Dictionary<string, object>();
                    parameters.Add("Id", id);

                    var result = dbHelper.ExecStorProcedureInt(procedureName, parameters);

                    AddToLog("Task removed", login);

                    return result;
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine("WinService Exception: " + ex.Message);
                throw;
            }
        }

        public int UpdateTask(Dictionary<string, object> parameters, string login)
        {
            try
            {
                using (var dbHelper = new DBHelper())
                {
                    const string procedureName = "update_task";

                    var result = dbHelper.ExecStorProcedureInt(procedureName, parameters);

                    AddToLog("Task updated", login);

                    return result;
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine("WinService Exception: " + ex.Message);
                throw;
            }
        }

        public DataTable GetUsersList()
        {
            try
            {
                using (var dbHelper = new DBHelper())
                {
                    const string procedureName = "get_users";

                    var result = dbHelper.ExecStorProcedureDS(procedureName).Tables[0];

                    return result;
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine("WinService Exception: " + ex.Message);
                throw;
            }
        }

        public DataTable GetProjectsList()
        {
            try
            {
                using (var dbHelper = new DBHelper())
                {
                    const string procedureName = "get_projects";

                    var result = dbHelper.ExecStorProcedureDS(procedureName).Tables[0];

                    return result;
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine("WinService Exception: " + ex.Message);
                throw;
            }
        }

        public DataTable GetLogsList()
        {
            try
            {
                using (var dbHelper = new DBHelper())
                {
                    const string procedureName = "get_logs";

                    var result = dbHelper.ExecStorProcedureDS(procedureName).Tables[0];

                    return result;
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine("WinService Exception: " + ex.Message);
                throw;
            }
        }

        public int ClearLogs()
        {
            try
            {
                using (var dbHelper = new DBHelper())
                {
                    const string procedureName = "remove_logs";

                    var parameters = new Dictionary<string, object>();

                    var result = dbHelper.ExecStorProcedureInt(procedureName, parameters);

                    return result;
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine("WinService Exception: " + ex.Message);
                throw;
            }
        }

        public DataTable GetUserStoriesListNames()
        {
            try
            {
                using (var dbHelper = new DBHelper())
                {
                    const string procedureName = "get_user_stories_list";

                    var result = dbHelper.ExecStorProcedureDS(procedureName).Tables[0];

                    return result;
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine("WinService Exception: " + ex.Message);
                throw;
            }
        }

        public DataTable GetUserStoriesList()
        {
            try
            {
                using (var dbHelper = new DBHelper())
                {
                    const string procedureName = "get_user_stories";

                    var result = dbHelper.ExecStorProcedureDS(procedureName).Tables[0];

                    return result;
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine("WinService Exception: " + ex.Message);
                throw;
            }
        }

        public int AddNewUserStory(Dictionary<string, object> parameters, string login)
        {
            try
            {
                using (var dbHelper = new DBHelper())
                {
                    const string procedureName = "add_user_story";

                    var result = dbHelper.ExecStorProcedureInt(procedureName, parameters);

                    AddToLog("User story added", login);

                    return result;
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine("WinService Exception: " + ex.Message);
                throw;
            }
        }

        public int RemoveUserStory(int id, string login)
        {
            try
            {
                using (var dbHelper = new DBHelper())
                {
                    const string procedureName = "remove_user_story";

                    var parameters = new Dictionary<string, object>();
                    parameters.Add("Id", id);

                    var result = dbHelper.ExecStorProcedureInt(procedureName, parameters);

                    AddToLog("User story removed", login);

                    return result;
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine("WinService Exception: " + ex.Message);
                throw;
            }
        }

        public int UpdateUserStory(Dictionary<string, object> parameters, string login)
        {
            try
            {
                using (var dbHelper = new DBHelper())
                {
                    const string procedureName = "update_user_story";

                    var result = dbHelper.ExecStorProcedureInt(procedureName, parameters);

                    AddToLog("User story updated", login);

                    return result;
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine("WinService Exception: " + ex.Message);
                throw;
            }
        }

        public DataTable GetIterationsListId()
        {
            try
            {
                using (var dbHelper = new DBHelper())
                {
                    const string procedureName = "get_iterations_list";

                    var result = dbHelper.ExecStorProcedureDS(procedureName).Tables[0];

                    return result;
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine("WinService Exception: " + ex.Message);
                throw;
            }
        }

        public DataTable GetIterationsList()
        {
            try
            {
                using (var dbHelper = new DBHelper())
                {
                    const string procedureName = "get_iterations";

                    var result = dbHelper.ExecStorProcedureDS(procedureName).Tables[0];

                    return result;
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine("WinService Exception: " + ex.Message);
                throw;
            }
        }

        public int AddNewIteration(Dictionary<string, object> parameters, string login)
        {
            try
            {
                using (var dbHelper = new DBHelper())
                {
                    const string procedureName = "add_iteration";

                    var result = dbHelper.ExecStorProcedureInt(procedureName, parameters);

                    AddToLog("Iteration added", login);

                    return result;
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine("WinService Exception: " + ex.Message);
                throw;
            }
        }

        public int RemoveIteration(int id, string login)
        {
            try
            {
                using (var dbHelper = new DBHelper())
                {
                    const string procedureName = "remove_iteration";

                    var parameters = new Dictionary<string, object>();
                    parameters.Add("Id", id);

                    var result = dbHelper.ExecStorProcedureInt(procedureName, parameters);

                    AddToLog("Iteration removed", login);

                    return result;
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine("WinService Exception: " + ex.Message);
                throw;
            }
        }

        public int UpdateIteration(Dictionary<string, object> parameters, string login)
        {
            try
            {
                using (var dbHelper = new DBHelper())
                {
                    const string procedureName = "update_iteration";

                    var result = dbHelper.ExecStorProcedureInt(procedureName, parameters);

                    AddToLog("Iteration updated", login);

                    return result;
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine("WinService Exception: " + ex.Message);
                throw;
            }
        }

        public DataTable GetReleasesList()
        {
            try
            {
                using (var dbHelper = new DBHelper())
                {
                    const string procedureName = "get_releases";

                    var result = dbHelper.ExecStorProcedureDS(procedureName).Tables[0];

                    return result;
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine("WinService Exception: " + ex.Message);
                throw;
            }
        }

        public int AddNewRelease(Dictionary<string, object> parameters, string login)
        {
            try
            {
                using (var dbHelper = new DBHelper())
                {
                    const string procedureName = "add_release";

                    var result = dbHelper.ExecStorProcedureInt(procedureName, parameters);

                    AddToLog("Release added", login);

                    return result;
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine("WinService Exception: " + ex.Message);
                throw;
            }
        }

        public int RemoveRelease(int id, string login)
        {
            try
            {
                using (var dbHelper = new DBHelper())
                {
                    const string procedureName = "remove_release";

                    var parameters = new Dictionary<string, object>();
                    parameters.Add("Id", id);

                    var result = dbHelper.ExecStorProcedureInt(procedureName, parameters);

                    AddToLog("Release removed", login);

                    return result;
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine("WinService Exception: " + ex.Message);
                throw;
            }
        }

        public int UpdateRelease(Dictionary<string, object> parameters, string login)
        {
            try
            {
                using (var dbHelper = new DBHelper())
                {
                    const string procedureName = "update_release";

                    var result = dbHelper.ExecStorProcedureInt(procedureName, parameters);

                    AddToLog("Release updated", login);

                    return result;
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine("WinService Exception: " + ex.Message);
                throw;
            }
        }

        public DataTable GetTeamStatusList()
        {
            try
            {
                using (var dbHelper = new DBHelper())
                {
                    const string procedureName = "get_team_status";

                    var result = dbHelper.ExecStorProcedureDS(procedureName).Tables[0];

                    return result;
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine("WinService Exception: " + ex.Message);
                throw;
            }
        }

        public DataTable GetTeamStatusListWithFilter(Dictionary<string, object> parameters)
        {
            try
            {
                using (var dbHelper = new DBHelper())
                {
                    const string procedureName = "get_team_status_with_filter";

                    var result = dbHelper.ExecStorProcedureDS(procedureName, parameters).Tables[0];

                    return result;
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine("WinService Exception: " + ex.Message);
                throw;
            }
        }

        public int AddFilesToTask(DataTable dataTable, int taskId)
        {
            try
            {
                using (var dbHelper = new DBHelper())
                {
                    if (!Directory.Exists("Uploads"))
                    {
                        Directory.CreateDirectory("Uploads");
                    }

                    if (Directory.Exists(string.Format("Uploads\\{0}", taskId)))
                        Directory.Delete(string.Format("Uploads\\{0}", taskId), true);

                    Directory.CreateDirectory(string.Format("Uploads\\{0}", taskId));

                    const string procedureRemoveName = "remove_documents";

                    var removeParameters = new Dictionary<string, object>();
                    removeParameters.Add("Task_id", taskId);

                    dbHelper.ExecStorProcedureInt(procedureRemoveName, removeParameters);

                    foreach (DataRow row in dataTable.Rows)
                    {
                        var path = string.Format("Uploads\\{0}\\{1}", taskId, row["Name"]);

                        var buffer = (byte[]) row["FileByte"];

                        var newFile = new FileStream(path, FileMode.Create);
                        // Write data to the file
                        newFile.Write(buffer, 0, buffer.Length);
                        // Close file
                        newFile.Close();

                        const string procedureName = "add_document";

                        var parameters = new Dictionary<string, object>();
                        parameters.Add("Task_id", taskId);
                        parameters.Add("Name", row["Name"]);
                        parameters.Add("Url", path);

                        dbHelper.ExecStorProcedureInt(procedureName, parameters);
                    }
                }

                return 1;

            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine("WinService Exception: " + ex.Message);
                throw;
            }
        }

        public DataTable GetFilesListByTask(int taskId)
        {
            try
            {
                using (var dbHelper = new DBHelper())
                {
                    const string procedureName = "get_documents";

                    var parameters = new Dictionary<string, object>();
                    parameters.Add("Task_id", taskId);

                    var result = dbHelper.ExecStorProcedureDS(procedureName, parameters).Tables[0];
                    var fileByteColumn = new DataColumn("FileByte", typeof(byte[]));

                    result.Columns.Add(fileByteColumn);

                    foreach (DataRow row in result.Rows)
                    {
                        if (!File.Exists(row["Url"].ToString()))
                            continue;

                        var fileBytes = File.ReadAllBytes(row["Url"].ToString());

                        row["FileByte"] = fileBytes;
                    }

                    return result;
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine("WinService Exception: " + ex.Message);
                throw;
            }
        }

        public DataTable GetBurnUpDown(int iterationId)
        {
            try
            {
                using (var dbHelper = new DBHelper())
                {
                    const string procedureName = "get_burnupdown";

                    var parameters = new Dictionary<string, object>();
                    parameters.Add("Iteration_id", iterationId);

                    var result = dbHelper.ExecStorProcedureDS(procedureName, parameters).Tables[0];

                    var totalActualSum = 0;

                    for (var i = 0; i < result.Rows.Count; i++ )
                    {
                        int parsedValue;
                        var isParsed = int.TryParse(result.Rows[i]["Actuals"].ToString(), out parsedValue);
                        if (!isParsed)
                            continue;

                        totalActualSum += parsedValue;
                        result.Rows[i]["Actuals"] = totalActualSum;
                        result.Rows[result.Rows.Count - 1 - i]["TO_DO"] = totalActualSum;
                    }



                    return result;
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine("WinService Exception: " + ex.Message);
                throw;
            }
        }

        public DataTable GetBurnUpDownChart(int iterationId)
        {
            try
            {
                using (var dbHelper = new DBHelper())
                {
                    const string procedureName = "get_chartdata";

                    var parameters = new Dictionary<string, object>();
                    parameters.Add("Iteration_id", iterationId);

                    var result = dbHelper.ExecStorProcedureDS(procedureName, parameters).Tables[0];

                    return result;
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine("WinService Exception: " + ex.Message);
                throw;
            }
        }

        public int ValidateUser(string login, string password)
        {
            try
            {
                using (var dbHelper = new DBHelper())
                {
                    var md5Hash = MD5.Create();
                    var hash = GetMd5Hash(md5Hash, password);
                    
                    var result = dbHelper.ExecStorProcedureInt("validate_user",
                                                               new List<SqlParameterWrapper>
                                                                   {
                                                                       new SqlParameterWrapper("Login", login,
                                                                                               SqlDbType.NVarChar),
                                                                       new SqlParameterWrapper("Password", hash,
                                                                                               SqlDbType.NVarChar)
                                                                   });
                    return result;
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine("WinService Exception: " + ex.Message);
                throw;
            }
        }

        private void AddToLog(string message, string login)
        {
            try
            {
                using (var dbHelper = new DBHelper())
                {

                    dbHelper.ExecStorProcedureInt("add_logs",
                                                               new List<SqlParameterWrapper>
                                                                   {
                                                                       new SqlParameterWrapper("User_id", GetUserIdByName(login),
                                                                                               SqlDbType.Int),
                                                                       new SqlParameterWrapper("Action", message,
                                                                                               SqlDbType.NVarChar)
                                                                   });
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine("WinService Exception: " + ex.Message);
                throw;
            }
        }

        private int GetUserIdByName(string login)
        {
            try
            {
                using (var dbHelper = new DBHelper())
                {

                    var result = dbHelper.ExecStorProcedureInt("get_userid",
                                                               new List<SqlParameterWrapper>
                                                                   {
                                                                       new SqlParameterWrapper("Login", login,
                                                                                               SqlDbType.NVarChar)
                                                                   });
                    return result;
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine("WinService Exception: " + ex.Message);
                throw;
            }
        }

        public int CreateUser(Dictionary<string, object> parameters, string login)
        {
            try
            {
                using (var dbHelper = new DBHelper())
                {
                    const string procedureName = "create_user";

                    MD5 md5Hash = MD5.Create();

                    parameters["Password"] = GetMd5Hash(md5Hash, parameters["Password"].ToString());

                    var result = dbHelper.ExecStorProcedureInt(procedureName, parameters);

                    AddToLog("User created", login);

                    return result;
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine("WinService Exception: " + ex.Message);
                throw;
            }
        }

        public int RemoveUser(int id, string login)
        {
            try
            {
                using (var dbHelper = new DBHelper())
                {
                    const string procedureName = "remove_user";

                    var parameters = new Dictionary<string, object>();
                    parameters.Add("Id", id);

                    var result = dbHelper.ExecStorProcedureInt(procedureName, parameters);

                    AddToLog("User removed", login);

                    return result;
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine("WinService Exception: " + ex.Message);
                throw;
            }
        }

        public int UpdateUser(Dictionary<string, object> parameters, string login)
        {
            try
            {
                using (var dbHelper = new DBHelper())
                {
                    const string procedureName = "update_user";

                    var result = dbHelper.ExecStorProcedureInt(procedureName, parameters);

                    AddToLog("User updated", login);

                    return result;
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine("WinService Exception: " + ex.Message);
                throw;
            }
        }

        public int UpdateUserWithPassword(Dictionary<string, object> parameters, string login)
        {
            try
            {
                using (var dbHelper = new DBHelper())
                {
                    const string procedureName = "update_user_with_pw";

                    MD5 md5Hash = MD5.Create();

                    parameters["Password"] = GetMd5Hash(md5Hash, parameters["Password"].ToString());

                    var result = dbHelper.ExecStorProcedureInt(procedureName, parameters);

                    AddToLog("User updated", login);

                    return result;
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine("WinService Exception: " + ex.Message);
                throw;
            }
        }

        public int CreateProject(Dictionary<string, object> parameters, string login)
        {
            try
            {
                using (var dbHelper = new DBHelper())
                {
                    const string procedureName = "create_project";

                    var result = dbHelper.ExecStorProcedureInt(procedureName, parameters);

                    AddToLog("Project created", login);

                    return result;
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine("WinService Exception: " + ex.Message);
                throw;
            }
        }

        public int RemoveProject(int id, string login)
        {
            try
            {
                using (var dbHelper = new DBHelper())
                {
                    const string procedureName = "remove_project";

                    var parameters = new Dictionary<string, object>();
                    parameters.Add("Id", id);

                    var result = dbHelper.ExecStorProcedureInt(procedureName, parameters);

                    AddToLog("Project removed", login);

                    return result;
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine("WinService Exception: " + ex.Message);
                throw;
            }
        }

        public int UpdateProject(Dictionary<string, object> parameters, string login)
        {
            try
            {
                using (var dbHelper = new DBHelper())
                {
                    const string procedureName = "update_project";

                    var result = dbHelper.ExecStorProcedureInt(procedureName, parameters);

                    AddToLog("Project updated", login);

                    return result;
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine("WinService Exception: " + ex.Message);
                throw;
            }
        }

        public int SaveChartData()
        {
            try
            {
                using (var dbHelper = new DBHelper())
                {
                    const string procedureName = "save_chartdata";

                    var result = dbHelper.ExecStorProcedureInt(procedureName);

                    return result;
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine("WinService Exception: " + ex.Message);
                throw;
            }
        }

        #region i_dunno_where_to_put_it(md5_get/verify_functions)

        static string GetMd5Hash(MD5 md5Hash, string input)
        {

            // Convert the input string to a byte array and compute the hash.
            byte[] data = md5Hash.ComputeHash(Encoding.UTF8.GetBytes(input));

            // Create a new Stringbuilder to collect the bytes// and create a string.
            StringBuilder sBuilder = new StringBuilder();

            // Loop through each byte of the hashed data // and format each one as a hexadecimal string.
            for (int i = 0; i < data.Length; i++)
            {
                sBuilder.Append(data[i].ToString("x2"));
            }

            // Return the hexadecimal string.
            return sBuilder.ToString();
        }

        // Verify a hash against a string.
        static bool VerifyMd5Hash(MD5 md5Hash, string input, string hash)
        {
            // Hash the input.
            string hashOfInput = GetMd5Hash(md5Hash, input);

            // Create a StringComparer an compare the hashes.
            StringComparer comparer = StringComparer.OrdinalIgnoreCase;

            if (0 == comparer.Compare(hashOfInput, hash))
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        #endregion
    }
}
